/*****************************************************************************
  Copyright ?2002-2004 by Martin Cook. All rights are reserved. If you like
  this code then feel free to go ahead and use it. The only thing I ask is 
  that you don't remove or alter my copyright notice. Your use of this 
  software is entirely at your own risk. I make no claims about the 
  reliability or fitness of this code for any particular purpose. If you 
  make changes or additions to this code then please clearly mark your code 
  as yours. If you have questions or comments then please contact me at: 
  martin@codegator.com
  
  Have Fun! :o)
*****************************************************************************/


using System;
using System.Data;
using System.Data.OleDb;
using System.Configuration;

using Microsoft.ApplicationBlocks.Data.OleDb;

using CG.Security.Configuration;
using CG.Security.Data.Configuration;

namespace CG.Security.Data.Access
{
	
	/// <summary>
	/// An Access specific implementation of IRoleRightData
	/// </summary>
	internal sealed class RoleRightData : IRoleRightData
	{

		// *******************************************************************
		// Attributes.
		// *******************************************************************

		#region Attributes.

		/// <summary>
		/// The SQL to create a new role right.
		/// </summary>
		private static readonly string SQL_CREATE = 
			"INSERT INTO cg_security_role_right (role_id, right_id) " +
			"VALUES(@role_id, @right_id)";

		/// <summary>
		/// The SQL to delete a role right.
		/// </summary>
		private static readonly string SQL_DELETE = 
			"DELETE FROM cg_security_role_right " +
			"WHERE role_id = @role_id AND right_id = @right_id";

		/// <summary>
		/// The SQL to find a list of rights by role.
		/// </summary>
		private static readonly string SQL_FIND_BY_ROLE = 
			"SELECT cg_security_role_right.role_id, " +
			"cg_security_role_right.right_id, cg_security_right.right_name " +
			"FROM cg_security_role_right " +
			"INNER JOIN cg_security_right ON " +
			"cg_security_right.right_id = cg_security_role_right.right_id " +
			"WHERE cg_security_role_right.role_id = @role_id " +
			"ORDER BY cg_security_right.right_name";

		/// <summary>
		/// The SQL to find a list of roles by right.
		/// </summary>
		private static readonly string SQL_FIND_BY_RIGHT = 
			"SELECT cg_security_role_right.role_id, " +
			"cg_security_role_right.right_id, cg_security_role.role_name " +
			"FROM cg_security_role_right " +
			"INNER JOIN cg_security_role ON " +
			"cg_security_role.role_id = cg_security_role_right.role_id " +
			"WHERE cg_security_role_right.right_id = @right_id " +
			"ORDER BY cg_security_role.role_name";

		/// <summary>
		/// Various parameter names.
		/// </summary>
		private static readonly string PARM_ROLE_ID = "@role_id";
		private static readonly string PARM_RIGHT_ID = "@right_id";

		#endregion
		
		// ******************************************************************
		// IRoleRightData implementation.
		// ******************************************************************

		#region IRoleRightData implementation.

		/// <summary>
		/// Creates a new association between a role and a right.
		/// </summary>
		/// <param name="roleID">The identifier for the role.</param>
		/// <param name="rightID">The identifier for the right.</param>
		public void Create(
			int roleID,
			int rightID
			)
		{

			// Read the runtime setup.
			DataSettings settings = SettingsManager.DataSettings;

			// Attempt to load the parameters.
			OleDbParameter[] parms = OleDbHelperParameterCache.GetCachedParameterSet(
				settings.ConnectionString,
				SQL_CREATE
				);

			// Did we fail?
			if (parms == null)
			{

				// Create the parameters.
				parms = new OleDbParameter[] 
				{
					new OleDbParameter(PARM_ROLE_ID, OleDbType.SmallInt),
					new OleDbParameter(PARM_RIGHT_ID, OleDbType.SmallInt)
				};

				// Store the parameters in the cache.
				OleDbHelperParameterCache.CacheParameterSet(
					settings.ConnectionString, 
					SQL_CREATE, 
					parms
					);

			} // End if we failed to load the parameters.

			// Assign values to the parameters.
			parms[0].Value = roleID;
			parms[1].Value = rightID;

			// Execute the SQL statement.
			OleDbHelper.ExecuteNonQuery(
				settings.ConnectionString, 
				CommandType.Text, 
				SQL_CREATE, 
				parms
				);

		} // End Create()

		// ******************************************************************

		/// <summary>
		/// Deletes an association between a role and a right.
		/// </summary>
		/// <param name="roleID">The identifier for the role.</param>
		/// <param name="rightID">The identifier for the right.</param>
		public void Delete(
			int roleID,
			int rightID
			)
		{

			// Read the runtime setup.
			DataSettings settings = SettingsManager.DataSettings;

			// Attempt to load the parameters.
			OleDbParameter[] parms = OleDbHelperParameterCache.GetCachedParameterSet(
				settings.ConnectionString,
				SQL_DELETE
				);

			// Did we fail?
			if (parms == null)
			{

				// Create the parameters.
				parms = new OleDbParameter[] 
				{
					new OleDbParameter(PARM_ROLE_ID, OleDbType.SmallInt),
					new OleDbParameter(PARM_RIGHT_ID, OleDbType.SmallInt)
				};

				// Store the parameters in the cache.
				OleDbHelperParameterCache.CacheParameterSet(
					settings.ConnectionString, 
					SQL_DELETE, 
					parms
					);

			} // End if we failed to load the parameters.

			// Assign values to the parameters.
			parms[0].Value = roleID;
			parms[1].Value = rightID;

			// Execute the SQL statement.
			OleDbHelper.ExecuteNonQuery(
				settings.ConnectionString,
				CommandType.Text, 
				SQL_DELETE, 
				parms
				);

		} // End Delete()

		// ******************************************************************

		/// <summary>
		/// Returns all the roles that are associated with a specified right.
		/// </summary>
		/// <param name="rightID">The identifier for the right.</param>
		/// <returns>A DataSet containing all the roles associated with the 
		/// specified right.</returns>
		public System.Data.DataSet FindByRight(
			int rightID
			)
		{

			// Read the runtime setup.
			DataSettings settings = SettingsManager.DataSettings;

			// Attempt to load the parameters.
			OleDbParameter[] parms = OleDbHelperParameterCache.GetCachedParameterSet(
				settings.ConnectionString,
				SQL_FIND_BY_RIGHT
				);

			// Did we fail?
			if (parms == null)
			{

				// Create the parameters.
				parms = new OleDbParameter[] 
				{
					new OleDbParameter(PARM_RIGHT_ID, OleDbType.SmallInt)
				};

				// Store the parameters in the cache.
				OleDbHelperParameterCache.CacheParameterSet(
					settings.ConnectionString, 
					SQL_FIND_BY_RIGHT, 
					parms
					);

			} // End if we failed to load the parameters.

			// Assign values to the parameters.
			parms[0].Value = rightID;

			// Execute the SQL statement.
			return OleDbHelper.ExecuteDataset(
				settings.ConnectionString,
				CommandType.Text, 
				SQL_FIND_BY_RIGHT, 
				parms
				);

		} // End FindByRight()

		// ******************************************************************

		/// <summary>
		/// Returns all the rights that are associated with a specified role.
		/// </summary>
		/// <param name="rightID">The identifier for the role.</param>
		/// <returns>A DataSet containing all the rights associated with the 
		/// specified role.</returns>
		public System.Data.DataSet FindByRole(
			int roleID
			)
		{

			// Read the runtime setup.
			DataSettings settings = SettingsManager.DataSettings;

			// Attempt to load the parameters.
			OleDbParameter[] parms = OleDbHelperParameterCache.GetCachedParameterSet(
				settings.ConnectionString,
				SQL_FIND_BY_ROLE
				);

			// Did we fail?
			if (parms == null)
			{

				// Create the parameters.
				parms = new OleDbParameter[] 
				{
					new OleDbParameter(PARM_ROLE_ID, OleDbType.SmallInt)
				};

				// Store the parameters in the cache.
				OleDbHelperParameterCache.CacheParameterSet(
					settings.ConnectionString, 
					SQL_FIND_BY_ROLE, 
					parms
					);

			} // End if we failed to load the parameters.

			// Assign values to the parameters.
			parms[0].Value = roleID;

			// Execute the SQL statement.
			return OleDbHelper.ExecuteDataset(
				settings.ConnectionString,
				CommandType.Text, 
				SQL_FIND_BY_ROLE, 
				parms
				);

		} // End FindByRole()

		#endregion 

	} // End class RoleRightData

} // End namespace CG.Security.Data.Access

